package com.gamutsoft.amb.common.dao.impl;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.Example;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Projections;
import org.springframework.orm.ObjectRetrievalFailureException;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import org.springframework.util.Assert;

import com.gamutsoft.amb.common.bean.CondictionsBean;
import com.gamutsoft.amb.common.bean.QueryExample;
import com.gamutsoft.amb.common.dao.IGenericDao;
import com.gamutsoft.amb.common.util.EcPageUtil;
import com.gamutsoft.amb.common.util.ListUtil;
import com.gamutsoft.amb.common.util.PageUtil;
import com.gamutsoft.amb.common.util.QueryUtil;



/**
 * This class serves as the Base class for all other DAOs - namely to hold
 * common CRUD methods that they might all use. You should only need to extend
 * this class when your require custom CRUD logic.
 * <p>
 * To register this class in your Spring context file, use the following XML.
 * 
 * <pre>
 *      &lt;bean id=&quot;fooDao&quot; class=&quot;org.appfuse.dao.hibernate.GenericDaoHibernate&quot;&gt;
 *          &lt;constructor-arg value=&quot;org.appfuse.model.Foo&quot;/&gt;
 *          &lt;property name=&quot;sessionFactory&quot; ref=&quot;sessionFactory&quot;/&gt;
 *      &lt;/bean&gt;
 * </pre>
 * 
 * @author <a href="mailto:bwnoll@gmail.com">Bryan Noll</a>
 * @param <T>
 *            a type variable
 * @param <PK>
 *            the primary key for that type
 */
public class GenericDaoHibernate<T, PK extends Serializable> extends
		HibernateDaoSupport implements IGenericDao<T, PK> {

	/**
	 * Log variable for all child classes. Uses LogFactory.getLog(getClass())
	 * from Commons Logging
	 */
	protected final Log log = LogFactory.getLog(getClass());
	private Class<T> persistentClass;
	protected String className;

	/**
	 * Constructor that takes in a class to see which type of entity to persist
	 * 
	 * @param persistentClass
	 *            the class type you'd like to persist
	 */
	public GenericDaoHibernate(final Class<T> persistentClass) {
		this.persistentClass = persistentClass;
		className = persistentClass.getSimpleName();
	}

	/**
	 * {@inheritDoc}
	 */
	public List<T> getAll() {
		return super.getHibernateTemplate().loadAll(this.persistentClass);
	}

	/**
	 * 根据条件精确查询，可分页或者不分
	 * 
	 * @param entity
	 * @param pageUtil
	 * @return
	 */
	public List<T> findByWhere(T entity, PageUtil pageUtil, Criterion... criterions) {
		List<T> results = null;
		
		// 如果Entity为空，则查询所有
		Session session = super.getSession();
		Criteria criteria = session.createCriteria(persistentClass);
		if (entity != null) {
			Example exampleEntity = Example.create(entity);
			exampleEntity.ignoreCase(); // 忽略大小写
		  //exampleEntity.enableLike(MatchMode.ANYWHERE);

			criteria.add(exampleEntity);
		}
		
		if (criterions != null) {
			for (Criterion criterion : criterions) {
				if (criterion != null) {
					criteria.add(criterion);
				}
			}
		}
		 
		if(pageUtil == null) {
			// 不分页
			results = (List<T>)criteria.list();
		} else {
			// 执行分页
			// 获取根据条件分页查询的总行数
			Long rowCount=0L;
			if(criteria.setProjection(Projections.rowCount()).uniqueResult() instanceof Integer)
			{
				Integer rowCount1 = (Integer)criteria.setProjection(Projections.rowCount()).uniqueResult();
				rowCount=rowCount1.longValue();
			}
			else
			{
				rowCount=(Long)criteria.setProjection(Projections.rowCount()).uniqueResult();
			}
			
			
			
			criteria.setProjection(null);
			
			pageUtil.setTotalRecord(rowCount.intValue());
			pageUtil.setPages();
			criteria.setFirstResult(pageUtil.getOffset());
			criteria.setMaxResults(pageUtil.getPageSize());
			results = (List<T>)criteria.list();
		}
		this.releaseHibernateSession(session);
		
		return results;
	}
	
	/**
	 * 根据条件模糊查询查询，可分页或者不分
	 * 
	 * @param entity
	 * @param pageUtil
	 * @return
	 */
	public List<T> findByWhereLike(T entity, PageUtil pageUtil, Criterion... criterions) {
		List<T> results = null;
		
		// 如果Entity为空，则查询所有
		Session session = super.getSession();
		Criteria criteria = session.createCriteria(persistentClass);
		if (entity != null) {
			Example exampleEntity = Example.create(entity);
			exampleEntity.ignoreCase(); // 忽略大小写
			exampleEntity.enableLike(MatchMode.ANYWHERE);

			criteria.add(exampleEntity);
		}
		
		if (criterions != null) {
			for (Criterion criterion : criterions) {
				if (criterion != null) {
					criteria.add(criterion);
				}
			}
		}
		
		if(pageUtil == null) {
			// 不分页
			results = (List<T>)criteria.list();
		} else {
			// 执行分页
			// 获取根据条件分页查询的总行数
			
			if (criteria.setProjection(Projections.rowCount()).uniqueResult() instanceof Long) {
				Long lngCount=(Long) criteria.setProjection(Projections.rowCount()).uniqueResult();
				Integer rowCount =lngCount.intValue(); 
				criteria.setProjection(null);
				
				pageUtil.setTotalRecord(rowCount.intValue());
				pageUtil.setPages();
				criteria.setFirstResult(pageUtil.getOffset());
				criteria.setMaxResults(pageUtil.getPageSize());
				results = (List<T>)criteria.list();
			}else{
				Integer lngCount=(Integer) criteria.setProjection(Projections.rowCount()).uniqueResult();
				Integer rowCount =lngCount.intValue(); 
				criteria.setProjection(null);
				
				pageUtil.setTotalRecord(rowCount.intValue());
				pageUtil.setPages();
				criteria.setFirstResult(pageUtil.getOffset());
				criteria.setMaxResults(pageUtil.getPageSize());
				results = (List<T>)criteria.list();
			}
		}
		this.releaseHibernateSession(session);
		
		return results;
	}
	/**
	 * {@inheritDoc}
	 */
	public T get(PK id) {
		T entity = (T) super.getHibernateTemplate().get(this.persistentClass,
				id);

		if (entity == null) {
			log.warn("Uh oh, '" + this.persistentClass + "' object with id '"
					+ id + "' not found...");
			throw new ObjectRetrievalFailureException(this.persistentClass, id);
		}

		return entity;
	}

	/**
	 * {@inheritDoc}
	 */
	public boolean exists(PK id) {
		T entity = (T) super.getHibernateTemplate().get(this.persistentClass,
				id);
		return entity != null;
	}

	/**
	 * {@inheritDoc}
	 */
	public T save(T object) {
		return (T) super.getHibernateTemplate().merge(object);

	}

	public void create(T entity) {
		try {
			super.getHibernateTemplate().persist(entity);
		} catch (Exception e) {
			log.error(e);
		}
	}

	/**
	 * {@inheritDoc}
	 */
	public void remove(PK id) {
		try {
			super.getHibernateTemplate().delete(this.get(id));
		} catch (RuntimeException e) {
			log.error(e);
		}
	}

	/**
	 * {@inheritDoc}
	 */
	public void deleteObjectsByFK(PK mainTableId, String fk) {
		String sql = "delete from " + this.persistentClass.getName()
				+ " where " + fk + " = ?";
		log.info(sql);
		this.getHibernateTemplate().bulkUpdate(sql, mainTableId);
	}

	/**
	 * {@inheritDoc}
	 */
	@SuppressWarnings("unchecked")
	public List<T> retrieveObjectByFK(PK mainTableId, String fk) {
		String sql = "from " + this.persistentClass.getName() + " where " + fk
				+ " = ?";
		log.info(sql);
		// System.out.println(sql);
		return getHibernateTemplate().find(sql, mainTableId);
		// getHibernateTemplate().find(sql,mainTableId);
	}

	/**
	 * {@inheritDoc}
	 */
	public Session getSpringHibernateSession() {

		return this.getSession();

	}

	/**
	 * {@inheritDoc}
	 */
	public void releaseHibernateSession(Session session) {

		this.releaseSession(session);
	}

	/**
	 * {@inheritDoc}
	 */
	public List findByPage(String sql, Object[] objs, int perPageStartNum,
			int perPageShowNum) {
		Session session = getSession();
		List list = null;
		Query query = session.createQuery(sql);
		for (int i = 0; i < objs.length; i++) {
			Object object = objs[i];
			query.setParameter(i, object);
		}
		if (query != null) {
			if (perPageStartNum > 0)
				query.setFirstResult(perPageStartNum);
			if (perPageShowNum > 0)
				query.setMaxResults(perPageShowNum);

			list = query.list();
		}
		releaseSession(session);
		return list;

	}

	@SuppressWarnings("unchecked")
	public List<T> findByPage(String sql, int perPageStartNum,
			int perPageShowNum, Object... objs) {
		Session session = getSession();
		List<T> list = null;
		Query query = session.createQuery(sql);
		for (int i = 0; i < objs.length; i++) {
			Object object = objs[i];
			query.setParameter(i, object);
		}
		if (query != null) {
			if (perPageStartNum > 0)
				query.setFirstResult(perPageStartNum);
			if (perPageShowNum > 0)
				query.setMaxResults(perPageShowNum);

			list = query.list();
		}
		releaseSession(session);
		return list;

	}

	@SuppressWarnings("unchecked")
	public List<T> findByPage(int currPageNum, int perPageShowNum, String sql,
			Object... objs) {
		int perPageStartNum = (currPageNum - 1) * perPageShowNum;
		Session session = getSession();
		List<T> list = null;
		Query query = session.createQuery(sql);
		for (int i = 0; i < objs.length; i++) {
			Object object = objs[i];
			query.setParameter(i, object);
		}
		if (query != null) {
			if (perPageStartNum > 0)
				query.setFirstResult(perPageStartNum);
			if (perPageShowNum > 0)
				query.setMaxResults(perPageShowNum);

			list = query.list();
		}
		releaseSession(session);
		return list;

	}

	public List<T> findByProperty(final String propertyName,
			final Object value, final String orderBy,
			final int... rowStartIdxAndCount) {
		Assert.hasLength(propertyName);

		String queryString = null;
		if (StringUtils.isBlank(orderBy)) {
			queryString = "select model from " + className
					+ " model where model." + propertyName + "= ?";
		} else {
			queryString = "select model from " + className
					+ " model where model." + propertyName + "= ? order by "
					+ orderBy;
		}
		int rowStartIdx = 0;
		int rowCount = 0;
		if (rowStartIdxAndCount != null && rowStartIdxAndCount.length > 0) {
			rowStartIdx = Math.max(0, rowStartIdxAndCount[0]);
			if (rowStartIdxAndCount.length > 1)
				rowCount = Math.max(0, rowStartIdxAndCount[1]);
		}

		return findByPage(queryString, rowStartIdx, rowCount, value);
	}

	public T findUniqueByProperty(final String propertyName, final Object value) {
		return singleResult(findByProperty(propertyName, value));
	}

	@SuppressWarnings("unchecked")
	public Object findUnique(final String queryString, final Object... values) {
		Assert.hasLength(queryString);
		return singleResult(this.getHibernateTemplate().find(queryString,
				values));
	}

	/**
	 * 取出列表的第一个对象. 如果列表为空返回Null,如果有多于一个对象,抛出异常.
	 */
	protected static <E> E singleResult(Collection<E> results) {
		if (results == null || results.isEmpty())
			return null;
		if (results.size() > 1)
			throw new IllegalArgumentException("the List size is more than 1");
		return results.iterator().next();
	}

	@SuppressWarnings({ "unchecked", "deprecation" })
	public List queryMapDataBySql(String sql, Object... values)
			throws Exception {
		Session session = this.getSpringHibernateSession();
		Connection conn = session.connection();
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		try {
			log.info(sql);
			pstmt = conn.prepareStatement(sql);
			if (values != null) {
				for (int i = 0; i < values.length; i++) {
					pstmt.setObject(i + 1, values[i]);
				}
			}
			rs = pstmt.executeQuery();
			int col = rs.getMetaData().getColumnCount();
			List<Map<String, Object>> tempList = new ArrayList<Map<String, Object>>();
			Map<String, Object> map = null;
			while (rs.next()) {
				map = new HashMap<String, Object>();
				for (int j = 0; j < col; j++) {
					map.put((rs.getMetaData().getColumnName(j + 1))
							.toLowerCase(), rs.getObject(j + 1));
				}
				tempList.add(map);
			}
			// conn.commit();
			closeConnection(conn, rs, pstmt);
			releaseSession(session);
			return tempList;

		} catch (SQLException e) {
			log.error(e);
			throw new Exception(e.getMessage());
		} finally {
			try {
				closeConnection(conn, rs, pstmt);
				if (session != null) {
					releaseSession(session);
				}
			} catch (SQLException e1) {
				log.error(e1);
				throw new Exception(e1.getMessage());

			}
			// session.close();
		}
	}
	@SuppressWarnings("unchecked")
	public String generateNoSeq() throws Exception{
		Session session = this.getSpringHibernateSession();
		Connection conn = session.connection();
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		String sqe = "";
		try {
			pstmt = conn.prepareStatement("select SEQ_SEQUENCE.Nextval from dual  ");
			rs = pstmt.executeQuery();
			int col = rs.getMetaData().getColumnCount();
			Map<String, Object> map = null;
			while (rs.next()) {
				for (int j = 0; j < col; j++) {
					sqe = String.valueOf(rs.getObject(j + 1));
					break;
				}
			}
			// conn.commit();
			closeConnection(conn, rs, pstmt);
			releaseSession(session);
		} catch (SQLException e) {
				log.error(e);
				throw new Exception(e.getMessage());
			} finally {
				try {
					closeConnection(conn, rs, pstmt);
					if (session != null) {
						releaseSession(session);
					}
				} catch (SQLException e1) {
					log.error(e1);
					throw new Exception(e1.getMessage());

				}
				// session.close();
			}
		return sqe;
	}
	
	@SuppressWarnings("unchecked")
	public List queryMapDataBySql(String sql, PageUtil pageUtil,
			Object... values) throws Exception {
		StringBuffer sb = new StringBuffer();

		int classNameIndex = sql.toLowerCase().indexOf("from");

		// 无法找到from，错误的语法
		if (classNameIndex == -1) {
			return null;
		}

		pageUtil.setTotalRecord(countRecordTotalBySql(sql, values));
		pageUtil.setPages();
//oracl分页
//		sb.append("select * from (select a.* , rownum rn from (").append(sql)
//				.append(") a where rownum <=")
//				.append(pageUtil.getOffset() + pageUtil.getPageSize())
//				.append(") where rn > ").append(pageUtil.getOffset());
		
//mysql 分页
		sb.append(sql);
		int startNum=(pageUtil.getCurPage()-1)*pageUtil.getPageSize();
		sb.append(" limit "+startNum+" , "+pageUtil.getPageSize());
		return queryMapDataBySql(sb.toString(), values);
	}

	@SuppressWarnings("unchecked")
	public List find(String hql, Object... values) {
		return this.getHibernateTemplate().find(hql, values);
	}

	/**
	 * 多条件查询
	 * 
	 * @param queryUtil
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List findByCondictions(QueryUtil queryUtil) {
		Object[] sqlObj = findByCondictions(queryUtil.getSelect(),
				queryUtil.getCondictions(), queryUtil.getHideCondiction(),
				queryUtil.getGroupBy(), queryUtil.getOrderBy(),
				queryUtil.getConRelation());

		Object[] objs = (Object[]) sqlObj[1];
		PageUtil pageUtil = queryUtil.getPageUtil();

		List result = null;
		if (sqlObj[1] == null) {
			if (pageUtil == null) {
				result = find(sqlObj[0].toString());
			} else {
				result = findByPage(sqlObj[0].toString(), pageUtil);
			}
		} else {
			if (pageUtil == null) {
				result = find(sqlObj[0].toString(), objs);
			} else {
				result = findByPage(sqlObj[0].toString(), pageUtil, objs);
			}
		}

		if (ListUtil.isEmpty(result)) {
			log.info("result size：0");
		} else {
			log.info("result size：" + result.size());
		}

		return result;
	}

	/**
	 * 多条件查询
	 * 
	 * @param queryUtil
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public List findByCondictionsBySql(QueryUtil queryUtil) throws Exception {
		Object[] sqlObj = findByCondictions(queryUtil.getSelect(),
				queryUtil.getCondictions(), queryUtil.getHideCondiction(),
				queryUtil.getGroupBy(), queryUtil.getOrderBy(),
				queryUtil.getConRelation());

		Object[] objs = (Object[]) sqlObj[1];
		PageUtil pageUtil = queryUtil.getPageUtil();

		if (sqlObj[1] == null) {
			if (pageUtil == null) {
				return queryMapDataBySql(sqlObj[0].toString());
			} else {
				return queryMapDataBySql(sqlObj[0].toString(), pageUtil);
			}
		} else {
			if (pageUtil == null) {
				return queryMapDataBySql(sqlObj[0].toString(), objs);
			} else {
				return queryMapDataBySql(sqlObj[0].toString(), pageUtil, objs);
			}
		}
	}

	public Object[] findByCondictions(String select,
			List<CondictionsBean> condictions, String hideCondiction,
			String groupBy, String order, String conRelation) {

		StringBuffer whereStr = new StringBuffer("");
		StringBuffer hql = new StringBuffer("");
		Object[] sqlObj = new Object[2];

		if (StringUtils.isNotBlank(select)) {
			hql = hql.append(select);
		}

		List<Object> values = new ArrayList<Object>();
		if (condictions != null) {
			List<CondictionsBean> temp = new ArrayList<CondictionsBean>();
			temp.addAll(condictions);

			for (int i = temp.size() - 1; i > -1; i--) {
				CondictionsBean condictionsBean = temp.get(i);

				if (condictionsBean.getValue() instanceof String) {
					String tempValue = (String) condictionsBean.getValue();
					if (StringUtils.isBlank(tempValue)) {
						condictionsBean.setValue(null);
					}
				}

				if (condictionsBean.getValue() == null
						&& !condictionsBean.getCondiction().equals("is")) {
					condictions.remove(i);
					continue;
				}

				if (condictionsBean.getCondiction().equals("like")) {
					condictionsBean.setValue("%" + condictionsBean.getValue()
							+ "%");
				}
			}
			temp.clear();

			int i = 0;
			for (CondictionsBean condictionsBean : condictions) {
				if (i == 0) {
					whereStr.append(" ");
					i++;
				} else {
					if (conRelation == null) {
						whereStr.append(" " + condictionsBean.getRelation()
								+ " ");

					} else {
						whereStr.append(" " + conRelation + " ");
					}
				}

				whereStr.append(condictionsBean.getStartStr());
				whereStr.append(condictionsBean.getProperty() + " "
						+ condictionsBean.getCondiction() + " ?");

				if (condictionsBean.getCondiction().equalsIgnoreCase("between")) {
					whereStr.append(" and ? ");
				}

				whereStr.append(condictionsBean.getEndStr());

				values.add(condictionsBean.getValue());

				if (condictionsBean.getCondiction().equalsIgnoreCase("between")) {
					values.add(condictionsBean.getAnotherValue());
				}
			}
		}

		if (StringUtils.isNotBlank(hideCondiction)) {
			if (whereStr.toString().trim().length() > 0) {
				whereStr.append(" " + hideCondiction + " ");
			} else {
				whereStr.append(" 1=1 " + hideCondiction + " ");
			}
		}

		if (whereStr.toString().trim().length() > 0) {
			hql = hql.append(" where " + whereStr.toString() + " ");
		}

		if (StringUtils.isNotBlank(groupBy))
			hql = hql.append(" group by " + groupBy);

		if (StringUtils.isNotBlank(order))
			hql = hql.append(" order by " + order);

		log.info("findByCondictions：" + hql.toString());

		if (!values.isEmpty()) {
			log.info("values：" + values);
			sqlObj[1] = values.toArray();
		}

		sqlObj[0] = hql.toString();
		return sqlObj;
	}

	@SuppressWarnings("unchecked")
	public List findByCondictions(Map<String, Object> condictions,
			String endCondiction, String fields[], String conRelation,
			String dtoClass, final int... rowStartIdxAndCount) {

		StringBuffer whereStr = new StringBuffer("");
		StringBuffer hql = new StringBuffer("");
		StringBuffer select = new StringBuffer("");

		if (fields != null)
			for (int i = 0; i < fields.length; i++) {
				if (StringUtils.isBlank(fields[i]))
					continue;

				if (!select.toString().equals(""))
					select.append(",");

				if (fields[i].contains("(") && fields[i].contains(")"))
					select.append(fields[i]);
				else
					select.append(fields[i]);
			}

		if (select.toString().trim().length() > 0)
			hql = hql.append("select " + select.toString());

		hql.append(" from " + dtoClass + " ");

		int parameter = 0;

		Object[] values = null;

		if (condictions != null) {

			values = new Object[condictions.size()];
			for (String key : condictions.keySet()) {
				if (parameter == 0)
					whereStr.append(" " + key + "=?");
				else
					whereStr.append(" " + conRelation + " " + key + "=?");

				values[parameter++] = condictions.get(key);
			}

		}

		if (whereStr.toString().trim().length() > 0)
			hql = hql.append(" where " + whereStr.toString());

		if (StringUtils.isNotBlank(endCondiction))
			hql = hql.append(" " + endCondiction);

		logger.info("findByCondictions：" + hql.toString());

		int rowStartIdx = 0;
		int rowCount = 0;
		if (rowStartIdxAndCount != null && rowStartIdxAndCount.length > 0) {
			rowStartIdx = Math.max(0, rowStartIdxAndCount[0]);
			if (rowStartIdxAndCount.length > 1)
				rowCount = Math.max(0, rowStartIdxAndCount[1]);
		}

		return findByPage(hql.toString(), rowStartIdx, rowCount, values);
	}

	@SuppressWarnings("unchecked")
	public List findByCondictions(List<CondictionsBean> condictions,
			PageUtil pageUtil, String order, String fields[], String dtoClass) {

		if (pageUtil instanceof EcPageUtil) {
			EcPageUtil ecPageUtil = (EcPageUtil) pageUtil;

			List<CondictionsBean> ecCondiction = ecPageUtil.getCondictions();

			if (condictions != null && ecCondiction != null) {
				condictions.addAll(ecCondiction);
			} else if (condictions == null && ecCondiction != null) {
				condictions = ecCondiction;
			}

			if (ecPageUtil.getSorts() != null) {
				order = ecPageUtil.getSorts();
			}
		}

		Object[] sqlObj = findByCondictions(condictions, order, fields, null,
				dtoClass);
		Object[] objs = (Object[]) sqlObj[1];

		if (sqlObj[1] == null) {
			if (pageUtil == null) {
				return find(sqlObj[0].toString());
			} else {
				return findByPage(sqlObj[0].toString(), pageUtil);
			}
		} else {
			if (pageUtil == null) {
				return find(sqlObj[0].toString(), objs);
			} else {
				return findByPage(sqlObj[0].toString(), pageUtil, objs);
			}
		}
	}

	public Object[] findByCondictions(List<CondictionsBean> condictions,
			String endCondiction, String fields[], String conRelation,
			String dtoClass) {

		StringBuffer whereStr = new StringBuffer("");
		StringBuffer hql = new StringBuffer("");
		StringBuffer select = new StringBuffer("");
		Object[] sqlObj = new Object[2];

		if (fields != null)
			for (int i = 0; i < fields.length; i++) {
				if (StringUtils.isBlank(fields[i]))
					continue;

				if (!select.toString().equals(""))
					select.append(",");

				if (fields[i].contains("(") && fields[i].contains(")"))
					select.append(fields[i]);
				else
					select.append(fields[i]);
			}

		if (select.toString().trim().length() > 0)
			hql = hql.append("select " + select.toString());

		hql.append(" from " + dtoClass + " ");

		Object[] values = null;
		if (condictions != null) {
			List<CondictionsBean> temp = new ArrayList<CondictionsBean>();
			temp.addAll(condictions);

			for (int i = temp.size() - 1; i > -1; i--) {
				CondictionsBean condictionsBean = temp.get(i);

				if (condictionsBean.getValue() == null
						&& condictionsBean.getCondiction().equals("=")) {
					condictions.remove(i);
					continue;
				}
			}
			temp.clear();

			int parameter = 0;
			values = new Object[condictions.size()];

			for (CondictionsBean condictionsBean : condictions) {
				if (parameter == 0) {
					whereStr.append(" ");
				} else {
					if (conRelation == null) {
						whereStr.append(" " + condictionsBean.getRelation()
								+ " ");

					} else {
						whereStr.append(" " + conRelation + " ");
					}
				}

				whereStr.append(condictionsBean.getStartStr());
				whereStr.append(condictionsBean.getProperty() + " "
						+ condictionsBean.getCondiction() + " ? ");

				if (condictionsBean.getCondiction().equalsIgnoreCase("between")) {
					whereStr.append(" and ? ");
				}

				whereStr.append(condictionsBean.getEndStr());

				values[parameter++] = condictionsBean.getValue();

				if (condictionsBean.getCondiction().equalsIgnoreCase("between")) {
					values[parameter++] = condictionsBean.getAnotherValue();
				}
			}
		}

		if (whereStr.toString().trim().length() > 0)
			hql = hql.append(" where " + whereStr.toString());

		if (StringUtils.isNotBlank(endCondiction))
			hql = hql.append(" " + endCondiction);

		log.info("findByCondictions：" + hql.toString());

		String valueStr = "values：";
		if (values != null) {
			for (int i = 0; i < values.length; i++) {
				Object object = values[i];
				if (i == 0)
					valueStr += object.toString();
				else
					valueStr += ", " + object.toString();
			}

			log.info(valueStr);
		}

		sqlObj[0] = hql.toString();
		sqlObj[1] = values;
		return sqlObj;
	}

	@SuppressWarnings("unchecked")
	public List findByCondictions(List<CondictionsBean> condictions,
			String fields[], String endCondiction, String conRelation,
			String dtoClass, final int... rowStartIdxAndCount) {

		int rowStartIdx = 0;
		int rowCount = 0;
		if (rowStartIdxAndCount != null && rowStartIdxAndCount.length > 0) {
			rowStartIdx = Math.max(0, rowStartIdxAndCount[0]);
			if (rowStartIdxAndCount.length > 1)
				rowCount = Math.max(0, rowStartIdxAndCount[1]);
		}

		Object[] sqlObj = findByCondictions(condictions, endCondiction, fields,
				conRelation, dtoClass);

		String hql = sqlObj[0].toString();
		Object[] values = (Object[]) sqlObj[1];

		return findByPage(rowStartIdx, rowCount, hql, values);
	}

	public void executeUpdate(String queryString, Object... values) {
		Session session = this.getSession();
		Query query = session.createQuery(queryString);

		for (int i = 0; i < values.length; i++) {
			query.setParameter(i, values[i]);
		}

		query.executeUpdate();
		releaseSession(session);
	}

	@SuppressWarnings("unchecked")
	public List findByPage(String queryString, PageUtil pageUtil,
			Object... objs) {
		int classNameIndex = queryString.toLowerCase().indexOf("from");

		// 无法找到from，错误的语法
		if (classNameIndex == -1) {
			return null;
		}

		pageUtil.setTotalRecord(countRecordTotal(
				queryString.substring(classNameIndex + 4), objs));
		pageUtil.setPages();
		return findByPage(queryString, pageUtil.getOffset(),
				pageUtil.getPageSize(), objs);
	}

	@SuppressWarnings("unchecked")
	public int countRecordTotal(String condition, Object... values) {
		int recordTotal = 0;
		String hql = "select count(*) from " + condition;
		List<Long> list = find(hql, values);
		recordTotal = list.get(0).intValue();

		return recordTotal;
	}

	public List<T> findByProperty(String propertyName, Object value,
			int... rowStartIdxAndCount) {
		return findByProperty(propertyName, value, "", rowStartIdxAndCount);
	}

	@SuppressWarnings("unchecked")
	public List findByPageBySql(int currPageNum, int perPageShowNum,
			String sql, Object... objs) {
		int perPageStartNum = (currPageNum - 1) * perPageShowNum;
		Session session = getSession();
		List<T> list = null;
		Query query = session.createSQLQuery(sql);
		for (int i = 0; i < objs.length; i++) {
			Object object = objs[i];
			query.setParameter(i, object);
		}
		if (query != null) {
			if (perPageStartNum > 0)
				query.setFirstResult(perPageStartNum);
			if (perPageShowNum > 0)
				query.setMaxResults(perPageShowNum);

			list = query.list();
		}
		releaseSession(session);
		return list;
	}

	@SuppressWarnings("unchecked")
	public List findByPageBySql(String sql, int perPageStartNum,
			int perPageShowNum, Object... objs) {
		Session session = getSession();
		List<T> list = null;
		Query query = session.createSQLQuery(sql);
		for (int i = 0; i < objs.length; i++) {
			Object object = objs[i];
			query.setParameter(i, object);
		}
		if (query != null) {
			if (perPageStartNum > 0)
				query.setFirstResult(perPageStartNum);
			if (perPageShowNum > 0)
				query.setMaxResults(perPageShowNum);

			list = query.list();
		}
		releaseSession(session);
		return list;
	}

	@SuppressWarnings("deprecation")
	public void updateBySql(String queryString, Object... values)
			throws Exception {
		Session session = this.getSpringHibernateSession();
		Connection conn = session.connection();
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		try {
			log.info("update by Sql:" + queryString);
			pstmt = conn.prepareStatement(queryString);
			if (values != null) {
				for (int i = 0; i < values.length; i++) {
					pstmt.setObject(i + 1, values[i]);
				}
			}
			pstmt.executeUpdate();

			// if( conn!=null ){
			// conn.commit();
			// }
			closeConnection(conn, rs, pstmt);
			releaseSession(session);

		} catch (SQLException e) {
			log.error(e);
			throw new Exception(e.getMessage());
		} finally {
			try {
				closeConnection(conn, rs, pstmt);
				if (session != null) {
					releaseSession(session);
				}
			} catch (SQLException e1) {
				log.error(e1);
				throw new Exception(e1.getMessage());

			}
		}
	}

	/*************************************************************************************************************************************************
	 * 关闭连接
	 * 
	 * @param conn
	 * @param rs
	 * @param pstmt
	 * @throws SQLException
	 */
	public void closeConnection(Connection conn, ResultSet rs,
			PreparedStatement pstmt) throws SQLException {
		if (rs != null) {
			rs.close();
		}
		if (pstmt != null) {
			pstmt.close();
		}
		if (conn != null) {
			conn.close();
		}
	}

	@SuppressWarnings("unchecked")
	public int countRecordTotalBySql(String condition, Object... values) {
		Session session = this.getSession();
		String sql = "";
		int recordTotal;
//		int classNameIndex = condition.toLowerCase().indexOf("from");

		// 无法找到from，错误的语法(oracle使用mysql不适用)
//		if (classNameIndex == -1) {
//			sql = "select count(*) from " + condition;
//		} else {
//			sql = "select count(*) from (" + condition + ")";
//		}
//		try {
//			Query query = session.createSQLQuery(sql);
//			if (values != null) {
//				for (int i = 0; i < values.length; i++) {
//					query.setParameter(i, values[i]);
//				}
//			}
//			List<Object> list = query.list();
//			if (list != null && list.size() > 0) {
//				recordTotal = new BigDecimal(list.get(0).toString()).intValue();
//			} else {
//				recordTotal = 0;
//			}
//
//		} catch (Exception e) {
//			log.error(e);
//			recordTotal = 0;
//		}
//		this.releaseHibernateSession(session);
//		return recordTotal;
		try {
			Query query = session.createSQLQuery(condition);
			if (values != null) {
				for (int i = 0; i < values.length; i++) {
					query.setParameter(i, values[i]);
				}
			}
			List<Object> list = query.list();
			recordTotal=list.size();
		} catch (Exception e) {
			log.error(e);
			recordTotal = 0;
		}
		this.releaseHibernateSession(session);
		return recordTotal;
	}

	@SuppressWarnings("unchecked")
	public List findByPageBySql(String queryString, PageUtil pageUtil,
			Object... objs) {
		return this.findByPageBySql(queryString, pageUtil.getOffset(),
				pageUtil.getPageSize(), objs);
	}

	@SuppressWarnings("unchecked")
	public List<T> getAll(String order) {
		String hql = "from " + this.className + " order by " + order;
		return find(hql);
	}

	/**
	 * 根据查询条件查询（暂时只支持SQL）
	 * 
	 * @author Vincent.luo
	 * @param sql
	 * @param queryExample
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Map> findByQueryExample(String sql, QueryExample queryExample) {
		List<CondictionsBean> condictions = queryExample.getCondictions();
		List<CondictionsBean> orCondictions = queryExample.getOrCondictions();
		Map<String, Map<String, Date>> timeCondictions = queryExample
				.getTimeCondictions();
		List<String> orderbyList = queryExample.getOrders();
		PageUtil pageUtil = queryExample.getPageUtil();
		StringBuffer sb = new StringBuffer(sql);
		LinkedList<Object> condictionList = new LinkedList<Object>();
		List<Map> result = null;
		if (condictions != null && condictions.size() > 0) {
			sb.append(" where ");
			for (int i = 0; i < condictions.size(); i++) {
				CondictionsBean condiction = condictions.get(i);
				if (i > 0) {
					sb.append(condiction.getRelation());
				}
				sb.append(" " + condiction.getProperty() + " "
						+ condiction.getCondiction() + " ? ");
				condictionList.add(condiction.getValue());
			}
			if (timeCondictions != null) {
				for (String key : timeCondictions.keySet()) {
					Map<String, Date> timeMap = timeCondictions.get(key);
					Date start = timeMap.get("start");
					Date end = timeMap.get("end");
					if (start != null && end != null) {
						sb.append(" and " + key + " between ? and ? ");
						condictionList.add(new Timestamp(start.getTime()));
						condictionList.add(new Timestamp(end.getTime()));
					} else if (start != null && end == null) {
						sb.append(" and " + key + " >= ?");
						condictionList.add(new Timestamp(start.getTime()));
					} else if (start == null && end != null) {
						sb.append(" and " + key + " <= ?");
						condictionList.add(new Timestamp(end.getTime()));
					}
				}
			}
		} else if (timeCondictions != null && timeCondictions.size() > 0) {
			sb.append(" where ");
			int i = 0;
			for (String key : timeCondictions.keySet()) {
				Map<String, Date> timeMap = timeCondictions.get(key);
				Date start = timeMap.get("start");
				Date end = timeMap.get("end");
				if (i != 0) {
					sb.append(" and ");
				}
				if (start != null && end != null) {
					sb.append(key + " between ? and ? ");
					condictionList.add(new Timestamp(start.getTime()));
					condictionList.add(new Timestamp(end.getTime()));
				} else if (start != null && end == null) {
					sb.append(key + " >= ?");
					condictionList.add(new Timestamp(start.getTime()));
				} else if (start == null && end != null) {
					sb.append(key + " <= ?");
					condictionList.add(new Timestamp(end.getTime()));
				}
				i++;
			}
		}
		if (orCondictions != null && orCondictions.size() > 0) {
			if (timeCondictions.size() == 0 && condictions.size() == 0) {
				sb.append(" where ");
			} else {
				sb.append(" and ");
			}
			if (orCondictions.size() > 0) {
				for (int i = 0; i < orCondictions.size(); i++) {
					CondictionsBean bean = orCondictions.get(i);
					if (i == 0) {
						sb.append(" ( ");
					} else {
						sb.append(" or ");
					}
					sb.append(bean.getProperty() + " " + bean.getCondiction()
							+ " ? ");
					condictionList.add(bean.getValue());
				}
				sb.append(" ) ");
			}

		}
		if (queryExample.getMySql() != null
				&& !queryExample.getMySql().equals("")) {
			if (timeCondictions.size() == 0 && condictions.size() == 0
					&& orCondictions.size() == 0) {
				sb.append(" where ");
			} else {
				sb.append(" and ");
			}
			sb.append(queryExample.getMySql());
		}
		sb.append(queryExample.getGroupBy());
		if (orderbyList != null && orderbyList.size() > 0) {
			sb.append(" order by ");
			for (int i = 0; i < orderbyList.size(); i++) {
				if (i == (orderbyList.size() - 1)) {
					sb.append(orderbyList.get(i));
				} else {
					sb.append(orderbyList.get(i) + ",");
				}
			}
		}

		try {
			if (pageUtil == null) {
				result = this.queryMapDataBySql(sb.toString(),
						condictionList.toArray());
			} else {
				// TODO 分页处理
				String temp = sb.toString();
				// String countSql = temp.substring(temp.indexOf( "from" ) + 4);
				// pageUtil.setPages( pageUtil.getPageSize(),
				// this.countRecordTotalBySql( temp, condictionList.toArray() )
				// );

				result = this.queryMapDataBySql(temp, pageUtil,
						condictionList.toArray());
				queryExample.setPageUtil(pageUtil);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
}
